package com.realtyManage.springboot.Dao;

import com.realtyManage.springboot.Model.Do.SpecialOfferHouse;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface SpecialOfferHouseRepository extends JpaRepository<SpecialOfferHouse,Integer> {

    List<SpecialOfferHouse> findByRelevanceHouseId(Integer relevanceHouseId);

    //int countByRelevanceHouseId(Integer relevanceHouseId);

    /*@Modifying
    @Transactional
    *//*@Query("update Article a set " +
            "a.title =  CASE WHEN :#{#article.title} IS NULL THEN a.title ELSE :#{#article.title} END ," +
            "a.pic =  CASE WHEN :#{#article.pic} IS NULL THEN a.pic ELSE :#{#article.pic} END ," +
            "a.content =  CASE WHEN :#{#article.content} IS NULL THEN a.pic ELSE :#{#article.pic} END ," +
            "a.addTime =  CASE WHEN :#{#article.addTime} IS NULL THEN a.addTime ELSE :#{#article.addTime} END ," +
            "a.showTime =  CASE WHEN :#{#article.showTime} IS NULL THEN a.showTime ELSE :#{#article.showTime} END ," +
            "a.state =  CASE WHEN :#{#article.state} IS NULL THEN a.state ELSE :#{#article.state} END " +
            "where a.id = :#{#article.id}")
    int update(@Param("article") Article article);*/

    /*Page<Article> findByStateAndTypeIn(Pageable pageable, int state, List<Integer> types);*/

    /*@Query(value = "SELECT * FROM article WHERE id= ?1", nativeQuery = true)
    List<Article> findByEndTimeAndRegion(int artId);*/

    @Query(value = "SELECT * FROM special_offer_house WHERE relevance_house_id= ?1 and time >= NOW() and is_show = 0",nativeQuery = true)
    List<SpecialOfferHouse> getTjHouse(String hid);

    @Query(value = "SELECT * FROM special_offer_house WHERE id= ?1 and time >= NOW() and is_show = 1",nativeQuery = true)
    SpecialOfferHouse getIdAll(Integer hid);

    @Query(value = "SELECT * FROM special_offer_house WHERE id= ?1 and time >= NOW() and is_show = 0",nativeQuery = true)
    SpecialOfferHouse getIdAllOne(Integer hid);

    @Query(value = "select  count(1) from  special_offer_house where  relevance_house_id = ?1 and  time >= NOW() and  is_show = 0 ",nativeQuery = true)
    int houseCount(Integer id);

    @Query(value = "select * from special_offer_house where  id =?1 and  time >= NOW() and  is_show = 0 ",nativeQuery = true)
    List<SpecialOfferHouse> findSpecialOfferHouseById(String id);

    @Query(value = "select * from special_offer_house where  relevance_house_id =?1 and  time >= NOW() and  is_show = 1 ",nativeQuery = true)
    List<SpecialOfferHouse> findSpecialOfferHouseByHouseId(String houserId);


    /*@Query(value = "UPDATE special_offer_house SET is_show =?1 WHERE id = ?2",nativeQuery = true)
    int updateSpecialOfferHouseType(String specialOfferHouseType,String specialOfferHouseId);*/

    @Modifying
    @Transactional
    @Query("update SpecialOfferHouse a set " +
            "a.isShow = CASE WHEN :#{#specialOfferHouseType} IS NULL THEN a.isShow ELSE :#{#specialOfferHouseType} END " +
            "where a.id = :#{#specialOfferHouseId}")
    int updateSpecialOfferHouseType(Integer specialOfferHouseType,Integer specialOfferHouseId);

    @Modifying
    @Transactional
    @Query("update SpecialOfferHouse a set " +
            "a.zt = CASE WHEN :#{#zt} IS NULL THEN a.zt ELSE :#{#zt} END, " +
            "a.sTime = CASE WHEN :#{#sTime} IS NULL THEN a.sTime ELSE :#{#sTime} END " +
            "where a.zt = :#{#id}")
    int updateZfTasks(Integer id, String sTime,Integer zt);


    @Modifying
    @Transactional
    @Query("update SpecialOfferHouse a set " +
            "a.name =  CASE WHEN :#{#specialOfferHouse.name} IS NULL THEN a.name ELSE :#{#specialOfferHouse.name} END ," +
            "a.architectureArea =  CASE WHEN :#{#specialOfferHouse.architectureArea} IS NULL THEN a.architectureArea ELSE :#{#specialOfferHouse.architectureArea} END ," +
            "a.indoorArea =  CASE WHEN :#{#specialOfferHouse.indoorArea} IS NULL THEN a.indoorArea ELSE :#{#specialOfferHouse.indoorArea} END ," +
            "a.houseCode =  CASE WHEN :#{#specialOfferHouse.houseCode} IS NULL THEN a.houseCode ELSE :#{#specialOfferHouse.houseCode} END ," +
            "a.price =  CASE WHEN :#{#specialOfferHouse.price} IS NULL THEN a.price ELSE :#{#specialOfferHouse.price} END ," +
            "a.buyNowPrice =  CASE WHEN :#{#specialOfferHouse.buyNowPrice} IS NULL THEN a.buyNowPrice ELSE :#{#specialOfferHouse.buyNowPrice} END, " +

            "a.time =  CASE WHEN :#{#specialOfferHouse.time} IS NULL THEN a.time ELSE :#{#specialOfferHouse.time} END ," +
            "a.logo =  CASE WHEN :#{#specialOfferHouse.logo} IS NULL THEN a.logo ELSE :#{#specialOfferHouse.logo} END ," +
            "a.newMake =  CASE WHEN :#{#specialOfferHouse.newMake} IS NULL THEN a.newMake ELSE :#{#specialOfferHouse.newMake} END, " +
            "a.oldMark =  CASE WHEN :#{#specialOfferHouse.oldMark} IS NULL THEN a.oldMark ELSE :#{#specialOfferHouse.oldMark} END ," +
            "a.handsel =  CASE WHEN :#{#specialOfferHouse.logo} IS NULL THEN a.logo ELSE :#{#specialOfferHouse.logo} END ," +
            "a.handsel =  CASE WHEN :#{#specialOfferHouse.handsel} IS NULL THEN a.handsel ELSE :#{#specialOfferHouse.handsel} END, " +
            "a.kkfw =  CASE WHEN :#{#specialOfferHouse.kkfw} IS NULL THEN a.kkfw ELSE :#{#specialOfferHouse.kkfw} END " +
            "where a.id = :#{#specialOfferHouse.id}")
    SpecialOfferHouse updateSpecialOfferHouse(@Param("specialOfferHouse") SpecialOfferHouse specialOfferHouse);


    @Query(value = "select  count(1) from  special_offer_house where  relevance_house_id = ?1 and  time >= NOW() and  is_show = 0 and zt = 0 ",nativeQuery = true)
    int dgCount(Integer id);

    @Query(value = "SELECT * FROM special_offer_house WHERE s_time <> \"\" AND zt = 1 AND is_show = 0 ",nativeQuery = true)
    List<SpecialOfferHouse> scanZt();

    @Modifying
    @Transactional
    @Query(value = " update special_offer_house set zt = 0,s_time = '' where id in (?1)",nativeQuery = true)
    int updateZt(String ids);

    @Query(value = "SELECT * FROM special_offer_house WHERE id= ?1 and time >= NOW() and is_show = 0 and zt = 0 ",nativeQuery = true)
    SpecialOfferHouse getId(Integer hid);
}


